const db = require("../db");
const { log } = require('console');
const db_execute = async (sql, params) => {
    if (!params) {
        return db.execute(sql, params); // 没有参数直接执行
    } else {
        const new_params = params.map(item => {
            if (typeof item === 'number') {
                return item.toString();  // 如果是数字，转换成字符串
            }
            return item;
        });
        return db.execute(sql, new_params); // 执行查询并传递转换后的参数
    }
};

class attendance {

    static async addAttendance(attendanceData) {
        try {
            const { employee_id, attendance_date, check_in_time, check_out_time, status } = attendanceData
            const query = `INSERT INTO employee_attendance (employee_id,attendance_date,check_in_time,check_out_time,status,created_at,updated_at) VALUES (?,?,?,?,?,NOW(),NOW())`
            const result = await db_execute(query, [employee_id, attendance_date, check_in_time, check_out_time, status])
            return result[0].affectedRows

        } catch (error) {
            throw new Error('添加考勤数据失败: ' + error.message);

        }
    }


    static async getAllAttendance(page = 1, limit = 10, searchMsg) {
        try {
            const pageNum = page - 1
            const pageSize = limit

            const offset = pageNum * pageSize
            let searchWord = []
            let queryParams = []
            if (searchMsg.employee_id) {
                searchWord.push(`employee_id = ? `)
                queryParams.push(Number(searchMsg.employee_id))
            }

            if (searchMsg.name) {
                searchWord.push(`name LIKE ? `)
                queryParams.push(`%${searchMsg.name}%`)
            }

            if (searchMsg.attendance_date) {
                searchWord.push(`attendance_date BETWEEN ? AND ? `)
                queryParams.push(searchMsg.attendance_date[0], searchMsg.attendance_date[1])
            }
            queryParams.push(pageSize, offset)
            let whereSQL = searchWord.length > 0 ? "WHERE " + searchWord.join("AND ") : ""

            const query = `SELECT * FROM employee_attendance ${whereSQL} LIMIT ? OFFSET ? `
            const [rows] = await db_execute(query, queryParams)
            //获取员工姓名
            for (const row of rows) {
                const employee_id = row.employee_id
                const username = await db_execute(`select name from employees where employee_id = ?`, [employee_id])
                row.name = username[0][0].name
            }

            queryParams = queryParams.slice(0, queryParams.length - 2)
            const countQuery = `SELECT COUNT(*) AS total FROM employee_attendance ${whereSQL}`
            const [countResult] = await db_execute(countQuery, queryParams)
            const total = countResult[0].total


            return {
                total,
                data: rows
            }
        } catch (err) {
            throw new Error('获取考勤数据失败: ' + err.message);
        }
    }

    static async updateAttendance(attendanceData) {
        try {
            // 从 attendanceData 中提取每个字段的值
            let { employee_id, attendance_date, check_in_time, check_out_time } = attendanceData;

            //判断考勤状态
            const today = new Date()
            const hour = today.getHours()
            const minute = today.getMinutes()
            let status = ""
            if (hour < 12) {
                check_out_time = null
                if (hour < 9 || (hour === 9 && minute === 0)) {
                    status = '出勤';
                } else {
                    status = '迟到';
                }
            } else {
                //获取对应考勤记录
                const attendance = await db_execute(`SELECT * FROM employee_attendance WHERE employee_id = ? AND attendance_date = ?`, [employee_id, attendance_date])
                if (attendance[0][0].check_in_time == null) {
                    status = '缺勤'
                } else {
                    check_in_time = attendance[0][0].check_in_time
                    if (hour > 17 || (hour === 17 && minute > 0)) {
                        status = '出勤';
                    } else {
                        status = '早退';
                    }
                }
            }

            // 构造 SQL 查询语句，使用占位符 ? 来避免 SQL 注入
            const query = `UPDATE employee_attendance SET 
                           check_in_time = ?, check_out_time = ?, status = ?, updated_at = NOW() 
                           WHERE employee_id = ? AND attendance_date = ?`;

            // 执行 SQL 查询，传递参数
            const result = await db_execute(query, [
                check_in_time, check_out_time, status, employee_id, attendance_date
            ]);
            // 返回受影响的行数，即更新的记录数
            return result[0].affectedRows;
        } catch (error) {
            // 如果出现错误，抛出异常
            throw new Error('更新考勤数据失败: ' + error.message);
        }
    }


    static async deleteAttendance(id) {
        try {
            const query = `DELETE FROM employee_attendance WHERE id = ?`
            const result = await db_execute(query, [id])
            return result[0].affectedRows
        } catch (err) {
            throw new Error('删除考勤数据失败: ' + err.message);
        }
    }
}

module.exports = attendance